﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace XinYue.Permission.Core
{
    public class DBHelper
    {
        private string connString = ConfigurationManager.AppSettings["connString"];
        private static string connectionString = ConfigurationManager.AppSettings["connString"];
        SqlConnection sqlConnection = null;
        int result = -1;
        /// <summary>
        /// 执行增删改返回受影响行数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public int ExcuteNoQuery(string sql, SqlParameter[] parameters)
        {
            //1：创建连接 
            sqlConnection = new SqlConnection(connString);
            //2:打开连接 
            sqlConnection.Open();
            //3；创建SqlCommand对象
            SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
            //4:预处理 
            if (parameters != null)
            {
                sqlCommand.Parameters.AddRange(parameters);
            }
            //5:执行sql语句 （ExcuteNonQuery）
            result = sqlCommand.ExecuteNonQuery();
            //关闭所有连接
            sqlConnection.Close();
            //6：处理返回结果、
            return result;
        }
        /// <summary>
        /// 执行查询返回一张表的数据(连接式)
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public SqlDataReader ExcuteReader(string sql, SqlParameter[] parameters)
        {
            SqlDataReader reader = null;
            //1：创建连接 
            sqlConnection = new SqlConnection(connString);
            //2:打开连接 
            sqlConnection.Open();
            //3；创建SqlCommand对象
            SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
            //4:预处理 
            if (parameters != null)
            {
                sqlCommand.Parameters.AddRange(parameters);
            }
            //5:执行sql语句 （ExecuteReader）
            reader = sqlCommand.ExecuteReader();
            //6：处理返回结果、
            return reader;
        }
        /// <summary>
        /// 执行查询返回一张表的数据(断开式)
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataSet GetDateSet(string sql, SqlParameter[] parameters)
        {
            DataSet dataSet = new DataSet();
            //1：创建连接 
            sqlConnection = new SqlConnection(connString);
            //2:打开连接 
            sqlConnection.Open();
            //3；创建SqlCommand对象
            SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
            //4:预处理 
            if (parameters != null)
            {
                sqlCommand.Parameters.AddRange(parameters);
            }
            //适配器
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            sqlDataAdapter.Fill(dataSet);
            //6：处理返回结果、
            return dataSet;
        }
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataSet QueryProcedure(string sql, SqlParameter[] parameters)
        {
            DataSet dataSet = new DataSet();
            //1：创建连接 
            sqlConnection = new SqlConnection(connString);
            //2:打开连接 
            sqlConnection.Open();
            //3；创建SqlCommand对象
            SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
            //指定执行存储过程
            sqlCommand.CommandType = CommandType.StoredProcedure;
            //预处理
            if (parameters != null)
            {
                sqlCommand.Parameters.AddRange(parameters);
            }
            //适配器
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            sqlDataAdapter.Fill(dataSet);
            //6：处理返回结果、
            return dataSet;
        }
        /// <summary>
        /// 查询返回单个数据的结果
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public int ExecuteScalar(string sql, SqlParameter[] parameters)
        {
            //创建连接
            sqlConnection = new SqlConnection(connString);
            //打开连接
            sqlConnection.Open();
            //创建SqlCommand对象
            SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
            //预处理
            if (parameters != null)
            {
                sqlCommand.Parameters.AddRange(parameters);
            }
            //执行sql语句 （ExcuteNonQuery）
            result = Convert.ToInt32(sqlCommand.ExecuteScalar());
            //关闭所有连接
            sqlConnection.Close();
            //处理返回结果
            return result;
        }

        //用于实现服务功能时所加的一段特有代码
        public static DataSet GetDataSet(string sql)
        {
            DataSet ds = new DataSet();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand sqlCommand = new SqlCommand(sql, conn);
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
                //
                sqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                sqlDataAdapter.Fill(ds);
                return ds;
            }
        }
    }
}
